﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using DBUtility;
using MyExection;
using System.Data.SqlClient;
using Model.Security.EntityInfo;
using Util;
using Model.Security.FindEntityInfo;
using Model.BusinessLogic.FindEntityInfo;
using Model.BusinessLogic.EntityInfo;

namespace BusinessLogicDAL
{
    /// <summary>
    /// 功能介绍：人员任务数据访问层
    /// </summary>
    public class RenYuanRenWuDAL
    {
        private readonly string conStr = ConfigurationManager.AppSettings["ERPDataConnStr"];

        public RenYuanRenWuDAL()
        {
        }
        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：查询DataTable信息
        /// </summary>
        /// <param name="FindEntity  findEntity"></param>
        /// <returns></returns>
        public DataTable SearchByFindEntity(FindRenYuanRenWuEntity findEntity)
        {
            try
            {
                string strSql = GetSearchSQLStrings();
                SqlParameter[] sqlParas = GetSearchParas(findEntity);
                DataTable tempDT = DBSQLHelper.GetDataTable(conStr, CommandType.Text, strSql, sqlParas);
                return tempDT;
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "ObjectDAL.SearchByFindEntity(FindEntity  findEntity)", "查询DataTable信息");
            }
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回参数赋值组
        /// </summary>
        /// <param name="FindEntity  findEntity"></param>
        /// <returns></returns>
        private SqlParameter[] GetSearchParas(FindRenYuanRenWuEntity findEntity)
        {
            SqlParameter[] sqlParameters = { 
                    new SqlParameter("@RY_MingCheng", findEntity.RY_MingCheng + '%'),
                    new SqlParameter("@RYRW_NianFen",'%' + findEntity.RYRW_NianFen + '%'),
                 };
            return sqlParameters;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetSearchSQLStrings()
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("	SELECT TB_RenYuan.RY_MingCheng,TB_RenYuan.RY_DengLuMing,TB_BuMen.BM_MingCheng,TB_RenYuanRenWu.RYRW_NianFen,TB_RenYuanRenWu.RYRW_Zhi,TB_RenYuanRenWu.RYRW_ID");
            sbSql.Append("	FROM TB_RenYuanRenWu ");
            sbSql.Append("	INNER JOIN TB_RenYuan ON TB_RenYuanRenWu.RY_ID = TB_RenYuan.RY_ID");
            sbSql.Append("	INNER JOIN TB_BuMen ON TB_RenYuan.BM_ID = TB_BuMen.BM_ID");
            sbSql.Append("	WHERE RY_MingCheng LIKE @RY_MingCheng");
            sbSql.Append("	AND RYRW_NianFen LIKE @RYRW_NianFen");
            sbSql.Append("  GROUP BY ");
            sbSql.Append("  RY_MingCheng, ");
            sbSql.Append("  RY_DengLuMing, ");
            sbSql.Append("  BM_MingCheng, ");
            sbSql.Append("  RYRW_NianFen, ");
            sbSql.Append("  RYRW_Zhi, ");
            sbSql.Append("  RYRW_ID ");
            return sbSql.ToString();
        }
        /// <summary>
        /// 创建时间：2010-7-12
        /// 功能介绍：添加RenYuanRenWuEntity
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool Add(SqlTransaction tran, RenYuanRenWuEntity entityInfo)
        {
            string strSql = GetAddSQLStrings();
            SqlParameter[] sqlParas = GetRenYuanRenWuEntityAddParas(entityInfo);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "RenYuanRenWuDAO.Add(RenYuanRenWuEntity entityInfo)", "根据实体添加数据");
            }
        }
        /// <summary>
        /// 创建时间：2010-7-12
        /// 功能介绍：添加RenYuanRenWuEntity
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool Add(RenYuanRenWuEntity entityInfo)
        {
            string strSql = GetAddSQLStrings();
            SqlParameter[] sqlParas = GetRenYuanRenWuEntityAddParas(entityInfo);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "RenYuanRenWuDAO.Add(RenYuanRenWuEntity entityInfo)", "根据实体添加数据");
            }
        }
        /// <summary>
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetAddSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO TB_RenYuanRenWu( RY_ID,RYRW_Zhi,RYRW_NianFen) values( @RY_ID,@RYRW_Zhi,@RYRW_NianFen)");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建时间:2010-7-12
        /// 功能简介:返回RenYuanRenWuEntity参数赋值组
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetRenYuanRenWuEntityAddParas(RenYuanRenWuEntity entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@RY_ID",entity.RY_ID),new SqlParameter("@RYRW_Zhi",entity.RYRW_Zhi),new SqlParameter("@RYRW_NianFen",entity.RYRW_NianFen)
               
                 };
            return parameter;
        }


        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：删除RenYuanRenWuEntity
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool Delete(string key)
        {
            string strSql = GetDeleteSQLStrings();
            SqlParameter[] sqlParas = GetDeleteEntityParas(key);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "RenYuanRenWuDAL.Delete(key)", "根据条件删除RenYuanRenWuEntity信息，同时返回删除结果");
            }
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetDeleteSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM TB_RenYuanRenWu WHERE RYRW_ID= @RYRW_ID");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回DeleteEntity参数赋值组
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetDeleteEntityParas(string key)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@RYRW_ID",key)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：更新RenYuanRenWuEntity
        /// </summary> 
        /// <param name="entityInfo"></param>
        /// <returns></returns>      
        public bool Update(RenYuanRenWuEntity entityInfo)
        {
            string strSql = GetUpdateSQLStrings();
            SqlParameter[] sqlParas = GetUpdateRenYuanRenWuEntity(entityInfo);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "RenYuanRenWuDAL.Update(RenYuanRenWuEntity entityInfo)", "更新实体信息");
            }
        }
        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetUpdateSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TB_RenYuanRenWu SET  RY_ID=@RY_ID,RYRW_Zhi=@RYRW_Zhi,RYRW_NianFen=@RYRW_NianFen WHERE  1 = 1 AND RYRW_ID= @RYRW_ID");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能简介：返回RenYuanRenWuEntity参数赋值
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetUpdateRenYuanRenWuEntity(RenYuanRenWuEntity entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@RYRW_ID",entity.RYRW_ID),new SqlParameter("@RY_ID",entity.RY_ID),new SqlParameter("@RYRW_Zhi",entity.RYRW_Zhi),new SqlParameter("@RYRW_NianFen",entity.RYRW_NianFen)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：根据“key”查询RenYuanRenWuEntity
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public RenYuanRenWuEntity GetListDetailById(string key)
        {
            DataRow row = null;
            RenYuanRenWuEntity renYuanRenWuEntity = new RenYuanRenWuEntity();
            StringBuilder sbSqlBuilder = new StringBuilder();
            try
            {
                sbSqlBuilder.Append("SELECT * FROM TB_RenYuanRenWu WHERE TB_RenYuanRenWu.RYRW_ID = @RYRW_ID");
                string sqlStr = sbSqlBuilder.ToString();
                SqlParameter[] parameters = new SqlParameter[]{
                        new SqlParameter("@RYRW_ID",key)
                    };
                DataTable RenYuanRenWuDT = new DataTable();
                RenYuanRenWuDT = DBSQLHelper.GetDataTable(conStr, CommandType.Text, sqlStr, parameters);
                if ((RenYuanRenWuDT != null) && (RenYuanRenWuDT.Rows.Count > 0))
                {
                    row = RenYuanRenWuDT.Rows[0];
                }
                else
                {
                    return renYuanRenWuEntity;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "RenYuanRenWuDAL.GetListDetailById(string key)", "获得RenYuanRenWu详细信息");
            }
            renYuanRenWuEntity.RYRW_ID = DataSwitch.ConvertObjectToInt32(row["RYRW_ID"]);
            renYuanRenWuEntity.RY_ID = DataSwitch.ConvertObjectToInt32(row["RY_ID"]);
            renYuanRenWuEntity.RYRW_Zhi = DataSwitch.ConvertObjectToDecimal(row["RYRW_Zhi"]);
            renYuanRenWuEntity.RYRW_NianFen = DataSwitch.ConvertObjectToString(row["RYRW_NianFen"]);

            return renYuanRenWuEntity;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllUserForYouXiao()
        {
            string strSql = "select * from TB_RenYuan where RY_ShiFouYouXiao = 1";
            try
            {
                return DBSQLHelper.GetDataTable(conStr, CommandType.Text, strSql, null);
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "RenYuanRenWuDAL.GetAllUserForYouXiao()", "获取有效地人员信息");
            }
        }
        /// <summary>
        /// 功能描述：根据年份删除人员任务
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool Del_RenYuanRenWuByNianFen(SqlTransaction trans, RenYuanRenWuEntity entityInfo)
        {
            return DBSQLHelper.ExecuteNonQuery(trans, CommandType.Text, GetDel_RenYuanRenWuByNianFen_SQL(), GetDel_RenYuanRenWuByNianFenParamter(entityInfo)) > 0;
        }
        /// <summary>
        /// 为方法：Del_RenYuanRenWuByNianFen提供SQL语句
        /// </summary>
        /// <returns></returns>
        private string GetDel_RenYuanRenWuByNianFen_SQL()
        {
            string strsql = "delete from TB_RenYuanRenWu where RYRW_NianFen=@RYRW_NianFen";
            return strsql;
        }
        /// <summary>
        /// 功能描述：为Del_RenYuanRenWuByNianFen方法的SQL语句赋值
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        private SqlParameter[] GetDel_RenYuanRenWuByNianFenParamter(RenYuanRenWuEntity entityInfo)
        {

            SqlParameter[] paramt =
            {            
            new SqlParameter("@RYRW_NianFen",entityInfo.RYRW_NianFen)
            };
            return paramt;
        }
        /// <summary>
        /// 根据年份查询人员任务
        /// </summary>
        /// <param name="nianFen"></param>
        /// <returns></returns>
        public DataTable GetRenYuanRenWuByNianFen(string nianFen)
        {

            return DBSQLHelper.GetDataTable(conStr, CommandType.Text, GetRenYuanRenWuByNianFenSQL(), GetGetRenYuanRenWuByNianFenparamter(nianFen));
        }
        /// <summary>
        /// 功能介绍：为GetRenYuanRenWuByNianFen方法提供SQL语句
        /// </summary>
        /// <returns></returns>
        private string GetRenYuanRenWuByNianFenSQL()
        {
            string strsql = "select * from TB_RenYuanRenWu where RYRW_NianFen=@RYRW_NianFen";
            return strsql;
        }
        /// <summary>
        /// 为GetRenYuanRenWuByNianFen方法的sql语句赋值
        /// </summary>
        /// <param name="nianFen"></param>
        /// <returns></returns>
        private SqlParameter[] GetGetRenYuanRenWuByNianFenparamter(string nianFen)
        {

            SqlParameter[] paramter =
                {
                
                    new SqlParameter("@RYRW_NianFen",nianFen)
                };
            return paramter;

        }

    }
}
